﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExcelToSqlserver
{
    public partial class ETOSMain : Form
    {

        #region Excel Property
        private ExcelACEHelper ACE = new ExcelACEHelper();
        #endregion
        public ETOSMain()
        {
            InitializeComponent();
        }

        private void OpenExcel(object sender, EventArgs e)
        {
            OpenExcelFile.ShowDialog(this);
        }

        #region 退出菜单
        private void QuitApplication(object sender, EventArgs e)
        {
            if (DialogResult.OK.Equals(MessageBox.Show("你确定要退出 Excel To SQLServer 吗？", "是否退出", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)))
            {
                Application.ExitThread();
            }
        }
        #endregion

        private void OpenExcelFileOK(object sender, CancelEventArgs e)
        {
            ACE.FileName = OpenExcelFile.FileName;
            ExcelFileOpenState.Text = OpenExcelFile.SafeFileName;
            ExcelFileOpenState.ToolTipText = OpenExcelFile.FileName;
        }
        private List<string> Dict_Table = new List<string>();
        private void AnalysisExcel(object sender, EventArgs e)
        {
            try
            {
                Dict_Table.Clear();
                TablesCheck.Items.Clear();
                #region 判断连接字符串
                if (string.IsNullOrWhiteSpace(ACE.ExcelConnectionString))
                {
                    MessageBox.Show("未打开任何Excel文件，无法进行分析，请先通过文件菜单的打开项打开一个Excel文件后再进行分析。", "未打开任何文件", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }
                #endregion
                TablesCheck.Items.AddRange(ACE.GetTables());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ExcelFileOpenState_TextChanged(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(ExcelFileOpenState.Text))
            {
                AnalysisExcelMenu.Enabled = false;
            }
            else
            {
                AnalysisExcelMenu.Enabled = true;
            }
        }

        private void zhm_a_Click(object sender, EventArgs e)
        {
            try
            {
                DGV.DataSource = ACE.Query(string.Format(Expression.Text, CurrectTableName));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private string CurrectTableName = string.Empty;
        private void TablesCheck_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                CurrectTableName = TablesCheck.SelectedItem.ToString();
                DGV.DataSource = ACE.Query("select * from [" + CurrectTableName + "]");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void gx_Click(object sender, EventArgs e)
        {
            try
            {
                ACE.Update(string.Format(Expression.Text, CurrectTableName));
                DGV.DataSource = ACE.Query("select * from [" + CurrectTableName + "]");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ExecuteQuery(object sender, EventArgs e)
        {
            try
            {
                string queryString = string.Empty;
                if (string.IsNullOrWhiteSpace(Expression.SelectedText))
                {
                    queryString = Expression.Text;
                }
                else
                {
                    queryString = Expression.SelectedText;
                }
                if ("select".Contains(queryString.Substring(0, 6).ToLower()))
                {
                    DGV.DataSource = ACE.Query(string.Format(queryString, "[" + CurrectTableName + "]"));
                }
                else
                {
                    int non = ACE.Update(string.Format(Expression.Text, "[" + CurrectTableName + "]"));
                    QueryNon.Text = string.Format("影响了{0}行结果。", non);
                    DGV.DataSource = ACE.Query("select * from [" + CurrectTableName + "]");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "异常", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }
        private string ImportFileName = string.Empty;
        private void CreateMO_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(SheetName.Text))
                {
                    MessageBox.Show("请填写Sheet名。", "sheet", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }
                SaveExcelFile.ShowDialog();
                ACE.CreateFile(ImportFileName, SheetName.Text, (DGV.DataSource as DataTable));

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void SaveExcelFile_FileOk(object sender, CancelEventArgs e)
        {
            ImportFileName = SaveExcelFile.FileName;
        }

        private void AppendMO_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(ImportFileName))
                {
                    SaveExcelFile.ShowDialog();
                }
                if (string.IsNullOrWhiteSpace(SheetName.Text))
                {
                    MessageBox.Show("请填写Sheet名。", "sheet", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }
                ACE.AppendFile(ImportFileName, SheetName.Text, (DGV.DataSource as DataTable));

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void GetSheetName_Click(object sender, EventArgs e)
        {
            NKeyValue.Clear();
            try
            {//select ID,MC as name from {0} where OPC is null
                SheetName.Items.Clear();
                DataTable dt = ACE.Query(string.Format("select ID,MC as name from {0} where OPC is null", "[" + CurrectTableName + "]"));
                foreach (DataRow dr in dt.Rows)
                {
                    SheetName.Items.Add(dr["name"]);
                    NKeyValue.Add(dr["name"].ToString(), int.Parse(dr["ID"].ToString()));
                }
            }
            catch
            {
                MessageBox.Show("请将select部分写成类似 select ID,MC as name from {0} where OPC is null");
            }
        }
        public Dictionary<string, int> NKeyValue = new Dictionary<string, int>();
        public Dictionary<string, string> CKeyValue = new Dictionary<string, string>();
        public void SetKeyValue(string key, string value)
        {
            CKeyValue[key] = value;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                CKeyValue.Clear();
                DataTable dt = ACE.Query(string.Format("select last([MC]) as [MC],last([OPC]) as [OPC] from {0} group by MC having last(OPC) is not null and last(MC) not like '测点名称'", "[" + CurrectTableName + "]"));
                foreach (DataRow dr in dt.Rows)
                {
                    CKeyValue.Add(dr["MC"].ToString(), "");
                }
                DGV.DataSource = dt;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //update {0} set Column = '' where MC = ''

        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                GetSheetName_Click(GetSheetName, e);
                SaveExcelFile.ShowDialog();
                bool create = false;
                foreach (var t in NKeyValue)
                {
                    DataTable dt = ACE.Query(string.Format("select [OPC],[Column],[MC] from {0} where [ID] = " + t.Value + " and [Column] is not null;", "[" + CurrectTableName + "]"));
                    if (!create)
                    {
                        ACE.CreateFile(ImportFileName, t.Key.Replace(" ", ""), dt);
                        create = true;
                    }
                    else
                    {
                        ACE.AppendFile(ImportFileName, t.Key.Replace(" ", ""), dt);
                    }
                    SheetName.Invoke(new Action(() =>
                    {
                        SheetName.SelectedItem = t.Key;
                    }));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {

            ColumnSet cs = new ColumnSet(this);
            cs.ShowDialog();

            //foreach (var t in CKeyValue)
            //{
            //    Console.WriteLine(t.Key + " => " + t.Value);
            //}
        }
        public void SetColumn(string key, string value)
        {
            try
            {
                string sql = "update {0} set [column] = '" + value + "' where [MC] = '" + key + "'";
                ACE.Update(string.Format(sql, "[" + CurrectTableName + "]"));
                DataTable dt = ACE.Query(string.Format("select last(MC) as MC,last(OPC) as OPC,last([Column]) as [Column] from {0} group by MC having last(OPC) is not null and last(MC) not like '测点名称'", "[" + CurrectTableName + "]"));
                DGV.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
